﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using NPOI.POIFS.FileSystem;
using NPOI.HPSF;


public partial class GiaBanHopDong : System.Web.UI.Page
{
    SqlParameter[] p;
    clsXuLy ex = new clsXuLy();
    string frmName = "frmGiaBanHopDong";
    static int f_SuDung = 0, f_Them = 0, f_Sua = 0, f_Xoa = 0, f_XemTatCa = 0, f_InBaoCao = 0;
    public string Header = System.Configuration.ConfigurationSettings.AppSettings["Header"].ToString();
    static DataTable dt;
    static int PageNumber = 6;
    static int CurrentPage = 1;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(Convert.ToString(Session["user"])))
            gstGetMess("Hết phiên làm việc, vui lòng đăng nhập để tiếp tục", "Default.aspx");
        else
        {
            if (!IsPostBack)
            {


                string idnguoidung = Session["IDUser"].ToString();
                string sql = "select SuDung,Them,Sua,Xoa,InBaoCao,XemTatCa from XDAPhanQuyen where IDnguoidung='" + idnguoidung + "' and tag='" + frmName + "'";

                DataTable dt2 = ex.GetData_Text(sql);
                if (dt2.Rows.Count > 0)
                {
                    f_SuDung = int.Parse(dt2.Rows[0]["SuDung"].ToString());
                    f_Them = int.Parse(dt2.Rows[0]["Them"].ToString());
                    f_Sua = int.Parse(dt2.Rows[0]["Sua"].ToString());
                    f_Xoa = int.Parse(dt2.Rows[0]["Xoa"].ToString());
                    f_InBaoCao = int.Parse(dt2.Rows[0]["InBaoCao"].ToString());
                    f_XemTatCa = int.Parse(dt2.Rows[0]["XemTatCa"].ToString());
                }
                if (f_SuDung.Equals(1))
                {
                    CurrentPage = 1;
                    btnCancel_Click(sender, e);
                    LoadCongTrinh(drCongTrinh);
                    LoadKhachHang(drkhachhang);
                    LoadMacHopDong(drmac);
                    Title = "Quản lý Giá bán hợp đồng | " + Header;

                    btnCancel_Click(sender, e);

                    for (int i = 1; i <= 12; i++)
                    {
                        dlThang.Items.Add(new ListItem("Tháng " + i.ToString(), i.ToString()));
                    }
                    dlThang.SelectedValue = DateTime.Now.Month.ToString();
                    int year1 = DateTime.Now.Year - 3;
                    int year2 = DateTime.Now.Year;
                    for (int i = year1; i <= year2; i++)
                    {
                        dlNam.Items.Add(new ListItem("Năm " + i.ToString(), i.ToString()));
                    }
                    dlNam.SelectedValue = year2.ToString();
                    LoadLoaiKhachHang();
                    LoadKhachHang();
                    //LoadCongTrinh();
                }
                else
                    gstGetMess("Bạn không có quyền xem trang này", "Home.aspx");

            }
        }
    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        if (f_Them.Equals(1))
        {
            try
            {
                p = new SqlParameter[11];
                p[0] = new SqlParameter("@IDs", Guid.NewGuid());
                p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                p[1] = new SqlParameter("@CongTrinh", ex.GetGuid(drCongTrinh.SelectedValue));
                p[1].SqlDbType = SqlDbType.UniqueIdentifier;
                p[2] = new SqlParameter("@MacHD", ex.GetGuid(drmac.SelectedValue));
                p[2].SqlDbType = SqlDbType.UniqueIdentifier;
                p[3] = new SqlParameter("@LoaiDa", drLoaiDa.SelectedValue);
                p[3].SqlDbType = SqlDbType.Int;
                p[4] = new SqlParameter("@GiaBan", double.Parse(txtgiaban.Text.Trim().Replace(",", "").Equals("") ? "0" : txtgiaban.Text.Trim()));
                p[4].SqlDbType = SqlDbType.Float;

                p[5] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text.Trim()));
                p[5].SqlDbType = SqlDbType.SmallDateTime;
                p[6] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text.Trim()));
                p[6].SqlDbType = SqlDbType.SmallDateTime;
                p[7] = new SqlParameter("@Vat", ckVAT.Checked ? 2 : 1);
                p[7].SqlDbType = SqlDbType.Int;
                p[8] = new SqlParameter("@NgayTao", DateTime.Now);
                p[8].SqlDbType = SqlDbType.SmallDateTime;
                p[9] = new SqlParameter("@NguoiTao", Session["user"].ToString());
                p[9].SqlDbType = SqlDbType.NVarChar;
                p[10] = new SqlParameter("@Del", 1);
                p[10].SqlDbType = SqlDbType.Int;
                ex.ExecuteNonQuerry("sp_xdaGiaBanHopDong_Add", p);
                btnCancel_Click(sender, e);
                gstGetMess("Lưu thành công", "");
            }
            catch (Exception ax)
            {
                gstGetMess("Có lỗi trong quá trình lưu, xem trong file log", "");
                ex.WriteLog(Server.MapPath("Log.txt"), DateTime.Now.ToString() + "\tGiá bán hợp đồng\t" + ax.Message);
            }
        }
        else
            gstGetMess("Bạn không có quyền thêm dữ liệu trên trang này", "");
    }
    protected void btnCancel_Click(object sender, EventArgs e)
    {
        drkhachhang.SelectedIndex = 0;
        drCongTrinh.SelectedIndex = 0;
        LoadCongTrinhFirst(drCongTrinh);
        txtgiaban.Text = "";
        ckVAT.Checked = false;
        txtTuNgay.Text = "";
        txtDenNgay.Text = "";
        drmac.SelectedIndex = 0;
        drLoaiDa.SelectedIndex = 0;
        drCongTrinh.Enabled = true;
        drkhachhang.Enabled = true;
        drmac.Enabled = true;
        p = new SqlParameter[2];
        p[0] = new SqlParameter("@PageNumber", 1);
        p[0].SqlDbType = SqlDbType.Int;
        p[1] = new SqlParameter("@RowspPage", PageNumber);
        p[1].SqlDbType = SqlDbType.Int;

        GV.DataSource = ex.GetData("sp_xdaGiaBanHopDong_List", p);
        GV.DataBind();
        btnSave.Visible = true;
        btncapnhat.Visible = false;
    }
    private void gstGetMess(string gstMess, string gstLink)
    {
        if (gstLink == "")
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "')", true);
        else
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "');window.location.href='" + gstLink + "'", true);

    }
    protected void GV_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        string ID = e.CommandArgument.ToString();
        hdID.Value = ID;
        Guid gID = new Guid(ID);
        p = new SqlParameter[1];
        p[0] = new SqlParameter("@IDs", gID);
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;

        if (e.CommandName.Equals("Select"))
        {
            DataTable dt = ex.GetData("sp_xdaGiaBanHopDong_ListByID", p);
            if (dt.Rows.Count > 0)
            {
                try
                {
                    //LoadCongTrinh(drCongTrinh);
                    drkhachhang.SelectedValue = dt.Rows[0]["makhachhang"].ToString();
                    drkhachhang_SelectedIndexChanged(sender, e);
                    drCongTrinh.SelectedValue = dt.Rows[0]["macongtrinh"].ToString();
                    txtgiaban.Text = dt.Rows[0]["giaban"].ToString();
                    ckVAT.Checked = dt.Rows[0]["vat"].ToString().Equals("2");
                    txtTuNgay.Text = dt.Rows[0]["tungay"].ToString();
                    txtDenNgay.Text = dt.Rows[0]["denngay"].ToString();
                    drmac.SelectedValue = dt.Rows[0]["mahd"].ToString();
                    drLoaiDa.SelectedValue = dt.Rows[0]["loaida"].ToString();

                    //drCongTrinh.Enabled = false;
                    //drkhachhang.Enabled = false;
                    //drmac.Enabled = false;

                }
                catch (Exception ax)
                {
                    ex.WriteLog(Server.MapPath("Log.txt"), DateTime.Now.ToString() + "\tGiá bán hợp đồng\t" + ax.Message);
                    gstGetMess(ax.Message, "");
                }

            }
            btncapnhat.Visible = true;
            btnSave.Visible = false;
        }
        else
            if (e.CommandName.Equals("Del"))
            {
                if (f_Xoa.Equals(1))
                {
                    try
                    {
                        ex.ExecuteNonQuerry("sp_xdaGiaBanHopDong_Del", p);
                        btnCancel_Click(sender, e);
                        gstGetMess("Xóa thành công", "");
                    }
                    catch (Exception ax)
                    {
                        ex.WriteLog(Server.MapPath("Log.txt"), DateTime.Now.ToString() + "\tGiá bán hợp đồng\t" + ax.Message);
                        gstGetMess(ax.Message, "");
                    }
                }
                else
                    gstGetMess("Bạn không có quyền xóa dữ liệu trên trang này", "");
            }
    }
    protected void btncapnhat_Click(object sender, EventArgs e)
    {
        if (f_Sua.Equals(1))
        {
            try
            {
                Guid gID = new Guid(hdID.Value);
                p = new SqlParameter[11];
                p[0] = new SqlParameter("@IDs", gID);
                p[0].SqlDbType = SqlDbType.UniqueIdentifier;

                p[1] = new SqlParameter("@CongTrinh", ex.GetGuid(drCongTrinh.SelectedValue));
                p[1].SqlDbType = SqlDbType.UniqueIdentifier;
                p[2] = new SqlParameter("@MacHD", ex.GetGuid(drmac.SelectedValue));
                p[2].SqlDbType = SqlDbType.UniqueIdentifier;
                p[3] = new SqlParameter("@LoaiDa", drLoaiDa.SelectedValue);
                p[3].SqlDbType = SqlDbType.Int;
                p[4] = new SqlParameter("@GiaBan", double.Parse(txtgiaban.Text.Trim().Replace(",", "").Equals("") ? "0" : txtgiaban.Text.Trim()));
                p[4].SqlDbType = SqlDbType.Float;

                p[5] = new SqlParameter("@TuNgay", ex.GetNgayThang(txtTuNgay.Text.Trim()));
                p[5].SqlDbType = SqlDbType.SmallDateTime;
                p[6] = new SqlParameter("@DenNgay", ex.GetNgayThang(txtDenNgay.Text.Trim()));
                p[6].SqlDbType = SqlDbType.SmallDateTime;
                p[7] = new SqlParameter("@Vat", ckVAT.Checked ? 2 : 1);
                p[7].SqlDbType = SqlDbType.Int;
                p[8] = new SqlParameter("@NgayTao", DateTime.Now);
                p[8].SqlDbType = SqlDbType.SmallDateTime;
                p[9] = new SqlParameter("@NguoiTao", Session["user"].ToString());
                p[9].SqlDbType = SqlDbType.NVarChar;
                p[10] = new SqlParameter("@Del", 1);
                p[10].SqlDbType = SqlDbType.Int;

                ex.ExecuteNonQuerry("sp_xdaGiaBanHopDong_Update", p);
                btnCancel_Click(sender, e);
                gstGetMess("Cập nhật thành công", "");
            }
            catch (Exception ax)
            {
                gstGetMess("Có lỗi trong quá trình cập nhật, xem trong file log", "");
                ex.WriteLog(Server.MapPath("Log.txt"), DateTime.Now.ToString() + "\tGiá bán hợp đồng\t" + ax.Message);
            }
        }
        else
            gstGetMess("Bạn không có quyền sửa dữ liệu trên trang này", "");
    }
    protected void btnPre_Click(object sender, EventArgs e)
    {
        if (CurrentPage > 1)
        {
            CurrentPage--;
            btnPre.Enabled = true;
            btnNext.Enabled = true;
            btnFirst.Enabled = true;

            p = new SqlParameter[2];
            p[0] = new SqlParameter("@PageNumber", CurrentPage);
            p[0].SqlDbType = SqlDbType.Int;
            p[1] = new SqlParameter("@RowspPage", PageNumber);
            p[1].SqlDbType = SqlDbType.Int;

            GV.DataSource = ex.GetData("sp_xdaGiaBanHopDong_List", p);
            GV.DataBind();

        }
        else
        {
            btnFirst.Enabled = false;
            btnPre.Enabled = false;
            btnNext.Enabled = true;
        }
    }
    protected void btnNext_Click(object sender, EventArgs e)
    {
        CurrentPage++;
        p = new SqlParameter[2];
        p[0] = new SqlParameter("@PageNumber", CurrentPage);
        p[0].SqlDbType = SqlDbType.Int;
        p[1] = new SqlParameter("@RowspPage", PageNumber);
        p[1].SqlDbType = SqlDbType.Int;

        GV.DataSource = ex.GetData("sp_xdaGiaBanHopDong_List", p);
        GV.DataBind();

        if (GV.Rows.Count.Equals(0))
        {
            btnPre.Enabled = true;
            btnNext.Enabled = false;
        }
        else
        {
            btnPre.Enabled = true;
            btnNext.Enabled = true;
        }
        btnFirst.Enabled = true;
    }
    protected void btnFirst_Click(object sender, EventArgs e)
    {
        CurrentPage = 1;
        p = new SqlParameter[2];
        p[0] = new SqlParameter("@PageNumber", CurrentPage);
        p[0].SqlDbType = SqlDbType.Int;
        p[1] = new SqlParameter("@RowspPage", PageNumber);
        p[1].SqlDbType = SqlDbType.Int;

        GV.DataSource = ex.GetData("sp_xdaGiaBanHopDong_List", p);
        GV.DataBind();

        btnPre.Enabled = false;
        btnNext.Enabled = true;
        btnFirst.Enabled = false;
    }

    void LoadKhachHang(DropDownList dl)
    {
        dl.Items.Clear();
        dl.DataSource = ex.GetData("sp_KhachHang_ListDrop");
        dl.DataBind();
        dl.Items.Insert(0, new ListItem("--Chọn khách hàng--", ""));
        dl.SelectedIndex = 0;
    }
    void LoadCongTrinh(DropDownList dl)
    {
        dl.Items.Clear();
        dl.DataSource = ex.GetData("sp_CongTrinh_ListDrop");
        dl.DataBind();
        dl.Items.Insert(0, new ListItem("--Chọn công trình--", ""));
        dl.SelectedIndex = 0;
    }
    void LoadCongTrinhFirst(DropDownList dl)
    {
        dl.Items.Clear();
        dl.Items.Insert(0, new ListItem("--Chọn công trình--", ""));
        dl.SelectedIndex = 0;
    }
    void LoadMacHopDong(DropDownList dl)
    {
        dl.Items.Clear();
        dl.DataSource = ex.GetData("sp_MacBeTong_ListDrop");
        dl.DataBind();
        dl.Items.Insert(0, new ListItem("--Chọn mác bê tông--", ""));
        dl.SelectedIndex = 0;
    }
   
    protected void btnTimKiem_Click(object sender, EventArgs e)
    {
        p = new SqlParameter[2];
        p[0] = new SqlParameter("@Thang", int.Parse(dlThang.SelectedValue));
        p[0].SqlDbType = SqlDbType.Int;
        p[1] = new SqlParameter("@Nam", int.Parse(dlNam.SelectedValue));
        p[1].SqlDbType = SqlDbType.Int;
        GV.DataSource = ex.GetData("sp_xdaGiaBanHopDong_Search", p);
        GV.DataBind();
    }

    protected void drkhachhang_SelectedIndexChanged(object sender, EventArgs e)
    {
        p = new SqlParameter[1];
        p[0] = new SqlParameter("@KH", ex.GetGuid(drkhachhang.SelectedValue));
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        drCongTrinh.DataSource = ex.GetData("sp_CongTrinh_ListDrop_TheoKhachHang", p);
        drCongTrinh.DataBind();
        drCongTrinh.Items.Insert(0, new ListItem("--Chọn công trình--", ""));
        drCongTrinh.SelectedIndex = 0;
    }
    protected void drctrinh()
    {
        p = new SqlParameter[1];
        p[0] = new SqlParameter("@KH", ex.GetGuid(drkh.SelectedValue));
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        drct.DataSource = ex.GetData("sp_CongTrinh_ListDrop_TheoKhachHang", p);
        drct.DataBind();
        drct.Items.Insert(0, new ListItem("--Chọn công trình--", ""));
        drct.SelectedIndex = 0;
    }
    void LoadLoaiKhachHang()
    {
        drloaikhachhang.Items.Clear();
        drloaikhachhang.DataSource = ex.GetData("sp_LoaiKh_ListDrop");
        drloaikhachhang.DataBind();
        drloaikhachhang.Items.Insert(0, new ListItem("--Chọn loại khách hàng--", ""));
        drloaikhachhang.SelectedIndex = 0;
    }
    void LoadKhachHang()
    {
        drkh.Items.Clear();
        //drkh.DataSource = ex.GetData("sp_KH_ListDrop");
        //drkh.DataBind();
        drkh.Items.Insert(0, new ListItem("--Chọn khách hàng--", ""));
        drkh.SelectedIndex = 0;
    }
    void LoadCongTrinh()
    {
        drct.Items.Clear();
        //drct.DataSource = ex.GetData("sp_CongTrinh_ListDrop");
        //drct.DataBind();
        drct.Items.Insert(0, new ListItem("--Chọn công trình--", ""));
        drct.SelectedIndex = 0;
    }
    void LoadMaCHD()
    {
        drMacHD.Items.Clear();
        //drct.DataSource = ex.GetData("sp_CongTrinh_ListDrop");
        //drct.DataBind();
        drMacHD.Items.Insert(0, new ListItem("--Chọn mác bê tông--", ""));
        drMacHD.SelectedIndex = 0;
    }
    protected void drloaikhachhang_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (drloaikhachhang.SelectedIndex > 0)
        {
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@Thang", int.Parse(dlThang.SelectedValue));
            p[0].SqlDbType = SqlDbType.Int;
            p[1] = new SqlParameter("@Nam", int.Parse(dlNam.SelectedValue));
            p[1].SqlDbType = SqlDbType.Int;
            p[2] = new SqlParameter("@Ids", ex.GetGuid(drloaikhachhang.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;
            GV.DataSource = ex.GetData("sp_xdagiabanhopdong_TkLoaiKH", p);
            GV.DataBind();

            p = new SqlParameter[1];
            p[0] = new SqlParameter("@Ids", ex.GetGuid(drloaikhachhang.SelectedValue));
            p[0].SqlDbType = SqlDbType.UniqueIdentifier;
            drkh.DataSource = ex.GetData("sp_KhachHang_TheoLoaiKh", p);
            drkh.DataBind();
            drkh.Items.Insert(0, new ListItem("--Chọn khách hàng--", ""));
            drkh.SelectedIndex = 0;
            
        }
        else
        {
            btnCancel_Click(sender, e);
            LoadKhachHang();
            

        }
        LoadCongTrinh();
        LoadMaCHD();
        btnPre.Visible = false;
        btnFirst.Visible = false;
        btnNext.Visible = false;
    }
    protected void drkh_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (drkh.SelectedIndex > 0)
        {
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@Thang", int.Parse(dlThang.SelectedValue));
            p[0].SqlDbType = SqlDbType.Int;
            p[1] = new SqlParameter("@Nam", int.Parse(dlNam.SelectedValue));
            p[1].SqlDbType = SqlDbType.Int;
            p[2] = new SqlParameter("@Ids", ex.GetGuid(drkh.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;
            GV.DataSource = ex.GetData("sp_xdagiabanhopdong_TkKH", p);
            GV.DataBind();
            drctrinh();
            
        }
        else
        {
            drloaikhachhang_SelectedIndexChanged(sender, e);
        }
        LoadMaCHD();
    }
    protected void drct_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (drct.SelectedIndex > 0)
        {
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@Thang", int.Parse(dlThang.SelectedValue));
            p[0].SqlDbType = SqlDbType.Int;
            p[1] = new SqlParameter("@Nam", int.Parse(dlNam.SelectedValue));
            p[1].SqlDbType = SqlDbType.Int;
            p[2] = new SqlParameter("@Ids", ex.GetGuid(drct.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;
            GV.DataSource = ex.GetData("sp_xdagiabanhopdong_CongTrinh", p);
            GV.DataBind();
            drmacbetong();
            drMacHD.Visible = true;
        }
        else
        {
            drkh_SelectedIndexChanged(sender, e);
            drMacHD.Visible = false;
        }
    }
    protected void drmacbetong()
    {
        p = new SqlParameter[1];
        p[0] = new SqlParameter("@Ids", ex.GetGuid(drct.SelectedValue));
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        drMacHD.DataSource = ex.GetData("sp_Mac_TheoCongTrinh", p);
        drMacHD.DataBind();
        drMacHD.Items.Insert(0, new ListItem("--Chọn mác bê tông--", ""));
        drMacHD.SelectedIndex = 0;
    }
    
    protected void drMacHD_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (drMacHD.SelectedIndex > 0)
        {
            p = new SqlParameter[4];
            p[0] = new SqlParameter("@Thang", int.Parse(dlThang.SelectedValue));
            p[0].SqlDbType = SqlDbType.Int;
            p[1] = new SqlParameter("@Nam", int.Parse(dlNam.SelectedValue));
            p[1].SqlDbType = SqlDbType.Int;
            p[2] = new SqlParameter("@Ids", ex.GetGuid(drct.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;
            p[3] = new SqlParameter("@Mac", ex.GetGuid(drMacHD.SelectedValue));
            p[3].SqlDbType = SqlDbType.UniqueIdentifier;
            GV.DataSource = ex.GetData("sp_xdagiabanhopdong_CongTrinh_Mac", p);
            GV.DataBind();
            drda.Visible = true;
           
        }
        else
        {
            drkh_SelectedIndexChanged(sender, e);
            drda.Visible = false;
        }
    }
    protected void drda_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (drda.SelectedIndex > 0)
        {
            p = new SqlParameter[5];
            p[0] = new SqlParameter("@Thang", int.Parse(dlThang.SelectedValue));
            p[0].SqlDbType = SqlDbType.Int;
            p[1] = new SqlParameter("@Nam", int.Parse(dlNam.SelectedValue));
            p[1].SqlDbType = SqlDbType.Int;
            p[2] = new SqlParameter("@Ids", ex.GetGuid(drct.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;
            p[3] = new SqlParameter("@Mac", ex.GetGuid(drMacHD.SelectedValue));
            p[3].SqlDbType = SqlDbType.UniqueIdentifier;
            p[4] = new SqlParameter("@loaida", drda.SelectedValue);
            p[4].SqlDbType = SqlDbType.Int;
            GV.DataSource = ex.GetData("sp_xdagiabanhopdong_CongTrinh_Mac_Loaida", p);
            GV.DataBind();
           

        }
        else
        {
            drMacHD_SelectedIndexChanged(sender, e);
        }
    }
    protected void btnPrint_Click(object sender, EventArgs e)
    {
        int index = 1;
        if (drloaikhachhang.SelectedIndex.Equals(0) && drkh.SelectedIndex.Equals(0) && drct.SelectedIndex.Equals(0))
            index = 1;
        else if (drloaikhachhang.SelectedIndex > 0 && drkh.SelectedIndex.Equals(0) && drct.SelectedIndex.Equals(0))
            index = 2;
        else if (drloaikhachhang.SelectedIndex > 0 && drkh.SelectedIndex > 0 && drct.SelectedIndex.Equals(0))
            index = 3;
        else if (drloaikhachhang.SelectedIndex > 0 && drkh.SelectedIndex > 0 && drct.SelectedIndex > 0)
            index = 4;

        p = new SqlParameter[5];
        p[0] = new SqlParameter("@Nam", int.Parse(dlNam.SelectedValue));
        p[0].SqlDbType = SqlDbType.Int;
        p[1] = new SqlParameter("@LoaiKH", ex.GetGuid(drloaikhachhang.SelectedValue));
        p[1].SqlDbType = SqlDbType.UniqueIdentifier;
        p[2] = new SqlParameter("@KhachHang", ex.GetGuid(drkh.SelectedValue));
        p[2].SqlDbType = SqlDbType.UniqueIdentifier;
        p[3] = new SqlParameter("@CongTrinh", ex.GetGuid(drct.SelectedValue));
        p[3].SqlDbType = SqlDbType.UniqueIdentifier;
        p[4] = new SqlParameter("@Index", index);
        p[4].SqlDbType = SqlDbType.Int;
        try
        {
            dt = ex.GetData("sp_xdagiabanhopdong_Print", p);
            PrintNPOI(dt);
        }
        catch (Exception ax)
        {
            gstGetMess(ax.Message, "");
        }

    }
    void PrintNPOI(System.Data.DataTable dt)
    {
        var workbook = new HSSFWorkbook();

        if (dt.Rows.Count > 0)
        {
            string sheetname = "Bảng giá bán hợp đồng";

            var sheet = workbook.CreateSheet(sheetname);

            #region CSS
            var headerLabelCellStyle = workbook.CreateCellStyle();
            headerLabelCellStyle.Alignment = HorizontalAlignment.LEFT;
            headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
            headerLabelCellStyle.BorderLeft = CellBorderType.THIN;
            headerLabelCellStyle.BorderRight = CellBorderType.THIN;
            headerLabelCellStyle.BorderTop = CellBorderType.THIN;

            var headerLabelFont = workbook.CreateFont();
            headerLabelFont.FontName = "Times New Roman";

            headerLabelFont.FontHeightInPoints = 11;
            headerLabelFont.Color = HSSFColor.BLACK.index;
            headerLabelCellStyle.SetFont(headerLabelFont);

            //--------------------------

            var hsRight = workbook.CreateCellStyle();
            hsRight.Alignment = HorizontalAlignment.RIGHT;
            hsRight.BorderBottom = CellBorderType.THIN;
            hsRight.BorderLeft = CellBorderType.THIN;
            hsRight.BorderRight = CellBorderType.THIN;
            hsRight.BorderTop = CellBorderType.THIN;

            hsRight.SetFont(headerLabelFont);
            //-------------------
            var hsCenter = workbook.CreateCellStyle();
            hsCenter.Alignment = HorizontalAlignment.CENTER;
            hsCenter.BorderBottom = CellBorderType.THIN;
            hsCenter.BorderLeft = CellBorderType.THIN;
            hsCenter.BorderRight = CellBorderType.THIN;
            hsCenter.BorderTop = CellBorderType.THIN;

            hsCenter.SetFont(headerLabelFont);
            //-----------------------

            var hs1 = workbook.CreateCellStyle();
            hs1.Alignment = HorizontalAlignment.LEFT;

            var hsb = workbook.CreateFont();
            hsb.Boldweight = (short)FontBoldWeight.BOLD;
            hsb.FontName = "Times New Roman";

            hsb.FontHeightInPoints = 11;
            hsb.Color = HSSFColor.BLACK.index;
            hs1.SetFont(hsb);

            //-------------
            var hs2 = workbook.CreateCellStyle();
            hs2.Alignment = HorizontalAlignment.CENTER;

            var hsb2 = workbook.CreateFont();
            hsb2.Boldweight = (short)FontBoldWeight.BOLD;
            hsb2.FontName = "Times New Roman";

            hsb2.FontHeightInPoints = 18;
            hsb2.Color = HSSFColor.BLACK.index;
            hs2.SetFont(hsb2);
            //------------------------------
            var hs3 = workbook.CreateCellStyle();
            hs3.Alignment = HorizontalAlignment.CENTER;
            //hs3.BorderBottom = CellBorderType.THIN;
            var hsb3 = workbook.CreateFont();
            hsb3.Boldweight = (short)FontBoldWeight.BOLD;
            hsb3.FontName = "Times New Roman";

            hsb3.FontHeightInPoints = 14;
            hsb3.Color = HSSFColor.BLACK.index;
            hs3.SetFont(hsb3);

            //----------------------------
            var hs4 = workbook.CreateCellStyle();
            hs4.Alignment = HorizontalAlignment.CENTER;

            hs4.BorderBottom = CellBorderType.THIN;
            hs4.BorderLeft = CellBorderType.THIN;
            hs4.BorderRight = CellBorderType.THIN;
            hs4.BorderTop = CellBorderType.THIN;

            var hsb4 = workbook.CreateFont();
            hsb4.Boldweight = (short)FontBoldWeight.BOLD;
            hsb4.FontName = "Times New Roman";

            hsb4.FontHeightInPoints = 11;
            hsb4.Color = HSSFColor.BLACK.index;
            hs4.SetFont(hsb4);

            #endregion

            //Start header-----------------------------------------------------------------------
            #region Header
            //tao hnag dau tien
            var rowIndex = 0;

            //cong ty
            var row = sheet.CreateRow(rowIndex);
            Cell r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 4);
            sheet.AddMergedRegion(cra);

            //chi nhanh
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            cra = new CellRangeAddress(1, 1, 0, 4);
            sheet.AddMergedRegion(cra);

            //tieu de bao cao
            string ngaythang = dlNam.SelectedItem.Text.ToUpper();
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("BẢNG GIÁ BÁN HỢP ĐỒNG " + ngaythang);
            r1c1.CellStyle = hs2;
            r1c1.Row.Height = 500;

            cra = new CellRangeAddress(2, 2, 0, 8);
            sheet.AddMergedRegion(cra);

            rowIndex++;

            //freeze panes
            sheet.CreateFreezePane(0, 6);
            #endregion
            
            #region Header1-2

            //header2
            rowIndex++;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            string[] header = { "Khách hàng", "Công trình", "Mác HĐ", "Loại đá", "Giá bán", "Từ ngày","Đến ngày", "VAT" };

            var cell = row.CreateCell(0);
            cell.SetCellValue("STT");
            cell.CellStyle = hs4;
            cell.Row.Height = 400;


            //tao tieu de cot
            for (int j = 0; j < header.Length; j++)
            {
                cell = row.CreateCell(j + 1);
                cell.SetCellValue(header[j].ToString());
                cell.CellStyle = hs4;
                cell.Row.Height = 500;
            }
            #endregion
            //End header-----------------------------------------------------------------------
            //ghi du lieu tung dong
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    cell = row.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                    if (j==0||j == 4 || j == 5 || j == 7 || j == 8)
                        cell.CellStyle = hsCenter;
                    else
                        if (j == 6)
                            cell.CellStyle = hsRight;
                    else
                        cell.CellStyle = headerLabelCellStyle;
                }
                cell.Row.Height = 400;

            }

            for (int ik = 0; ik < dt.Columns.Count; ik++)
            {
                sheet.AutoSizeColumn(ik);
            }
                //end sheet tong hop--------------------------
                using (var exportData = new MemoryStream())
                {
                    workbook.Write(exportData);

                    string saveAsFileName = "Bảng giá bán hợp đồng " + ngaythang + ".xls";

                    Response.ContentType = "application/vnd.ms-excel";
                    Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
                    Response.Clear();
                    Response.BinaryWrite(exportData.GetBuffer());
                    Response.End();
                }
        }
    }
    
}